package org.neframework.webtest.service;

import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.neframework.webtest.util.config.Config;
import org.neframework.webtest.util.date.DateTools;

import com.tmsps.neframework.core.common.page.Page;
import com.tmsps.neframework.core.utils.ChkTools;
import com.tmsps.neframework.jpa.base.BaseService;
import com.tmsps.neframework.jpa.sql.param.NeParamList;

public class CustomerAnysisService extends BaseService {

	public static void main(String[] args) {
		CustomerAnysisService s = new CustomerAnysisService();
		String sql = "select * from t_s_customer_card_pos t where t.status=0 and  {fn TIMESTAMPDIFF(SQL_TSI_SECOND,t.created, ?)}<=0 and {fn TIMESTAMPDIFF(SQL_TSI_SECOND,t.created, ?)}>0";
		List<Map<String, Object>> list = s.jc.queryForList(sql,
				new Object[] { DateTools.strToTimestamp("2015-07-07"),
						DateTools.strToTomarrowTimestamp("2015-07-07") });
		System.err.println(DateTools.strToTimestamp("2015-07-07"));
		System.err.println(DateTools.strToTomarrowTimestamp("2015-07-07"));
		System.err.println(list);
	}

	// 获取具体到某一天
	private String getDay(String srh_date) {
		if (ChkTools.isNull(srh_date)) {
			return "0";
		}
		return srh_date.split("-")[2];
	}

	public List<Map<String, Object>> selectDoitList(String srh_date,
			Map<String, String> sort_params, Page page) {
		// TODO 查询 还款信用卡列表
		Timestamp queryDay = DateTools.addDay(
				DateTools.strToTimestamp(srh_date),
				Config.getConfig().ALERT_DAY);

		String sql = "select t.*,tcust.name,tcust.mobile,tb.name bank_name from t_s_customer_card t "
				+ " left outer join t_s_bank tb on t.bank_id=tb.kid "
				+ " left outer join t_s_customer tcust on t.customer_id=tcust.kid "
				+ " where t.status=0 and (t.bill_back_day=?) ";

		NeParamList params = NeParamList.makeParams();
		params.add(getDay(DateTools.parse(queryDay)));

		List<Map<String, Object>> list = jcn.queryForList(sql, params,
				sort_params, page);
		return list;
	}

	public List<Map<String, Object>> selectDoitZdList(String srh_date,
			Map<String, String> sort_params, Page page) {
		// TODO 查询客户列表
		String sql = "select t.*,tcust.name,tcust.mobile,tb.name bank_name from t_s_customer_card t "
				+ " left outer join t_s_bank tb on t.bank_id=tb.kid "
				+ " left outer join t_s_customer tcust on t.customer_id=tcust.kid "
				+ " where t.status=0 and (t.bill_day=?) ";

		NeParamList params = NeParamList.makeParams();
		params.add(getDay(srh_date));

		List<Map<String, Object>> list = jcn.queryForList(sql, params,
				sort_params, page);
		return list;
	}

	public List<Map<String, Object>> selectFlowList(String srh_date,
			String srh_date2, String srh_card_no, String srh_address,
			Map<String, String> sort_params, Page page) {
		// TODO 刷卡流水
		String sql = "select t.*,t.card_no card_no_pos,t.created created_pos,tcust.name,tcust.mobile,tb.name bank_name,tcard.money_max,tcard.bill_day,tcard.bill_back_day "
				+ " from t_s_customer_card_pos t "
				+ " left outer join t_s_customer tcust on t.customer_id=tcust.kid "
				+ " left outer join t_s_customer_card tcard on t.customer_card_id=tcard.kid "
				+ " left outer join t_s_bank tb on tcard.bank_id=tb.kid "
				+ " where t.status=0 and ({fn TIMESTAMPDIFF(SQL_TSI_SECOND,t.created,?)}<=0) and ({fn TIMESTAMPDIFF(SQL_TSI_SECOND,t.created,?)}>=0) and (t.card_no like ?) and (t.address like ?) ";

		NeParamList params = NeParamList.makeParams();
		params.add(DateTools.strToTimestamp(srh_date));
		params.add(DateTools.strToTomarrowTimestamp(srh_date2));
		params.addLike(srh_card_no);
		params.addLike(srh_address);

		List<Map<String, Object>> list = jcn.queryForList(sql, params,
				sort_params, page);
		return list;
	}

	public Map<String, Object> findFlowMap(String customer_card_id, String date) {
		// TODO 查询卡片交易流水

		String sql = "select sum(t.money) money_sum,sum(case when type='还款' then t.money*(-1) else t.money end) money_sum_lr "
				+ " from t_s_customer_card_pos t"
				+ " where t.status=0 and (t.customer_card_id=?) and  ({fn TIMESTAMPDIFF(SQL_TSI_SECOND,t.created, ?)}<=0) and ({fn TIMESTAMPDIFF(SQL_TSI_SECOND,t.created,?)}>0) ";

		NeParamList params = NeParamList.makeParams();
		params.add(customer_card_id);
		params.add(DateTools.strToTimestamp(date));
		params.add(DateTools.strToTomarrowTimestamp(date));

		List<Map<String, Object>> list = jcn.queryForList(sql, params, null,
				new Page());

		Map<String, Object> flowMap = new HashMap<>();
		if (list.size() > 0) {
			flowMap = list.get(0);
		}

		return flowMap;
	}

}
